Example 14-1
------------------------------------------------------------------------------------------------
Use mydatabase
Go

GRANT INSERT, UPDATE, DELETE
ON tblCustomers
TO myusers, John, Tom
GO


Example 14-2
------------------------------------------------------------------------------------------------
Use mydatabase
Go

GRANT INSERT, UPDATE, DELETE
ON tblCustomers
TO myusers, John, [MySolution\Tom]



Example 14-3
------------------------------------------------------------------------------------------------
Use mydatabase
Go

GRANT SELECT
ON tblCustomers
TO public
GO




Example 14-4
------------------------------------------------------------------------------------------------

Use mydatabase
Go

DENY INSERT, UPDATE, DELETE
ON tblCustomers
TO myusers, John, Tom
GO



Example 14-5
------------------------------------------------------------------------------------------------

Use mydatabase
Go

DENY SELECT
ON tblCustomers
TO Accounting
GO



Example 14-6
------------------------------------------------------------------------------------------------
select password 
from master.dbo.sysxlogins
where name='sa'


Example 14-7
------------------------------------------------------------------------------------------------
select pwdcompare('sa',
(select top 1 password 
from master.dbo.sysxlogins
where name='sa'),0)



Example 14-8
------------------------------------------------------------------------------------------------
select pwdencrypt('huukhang.com')

Example 14-9
------------------------------------------------------------------------------------------------
select encrypt('huukhang.com')


Example 14-10
------------------------------------------------------------------------------------------------
CREATE Proc myPros
	@No int,
	@Result float output
	With Encryption
As
	Declare @total int, @j int	
	Declare @str varchar(50)
	Set @j=10
	Set @j=@j+(select count(*) 
			from tblCustomers)
	Select @j=@j+10
	Select @j=@j+ count(*),
	@total=sum(Amount) from tblInvoiceDetails
	Where ItemID=@No	
	set @Result=@total/2
	return @total
	
GO


Example 14-11
------------------------------------------------------------------------------------------------
create  PROCEDURE sp_decrypt 
	@objectName varchar(50)
AS
	/*Doc trong sach thu tuc dung de DeCrypt SP, Trigger, View, Function da ma hoa*/
GO


Example 14-12
------------------------------------------------------------------------------------------------
exec sp_decrypt 'myPros'


Example 14-13
------------------------------------------------------------------------------------------------
Dim myCon,myRst
		Dim strSQL,strUser, strPwd
		strUser=request.form(txtUser)
		strPwd=request.form(txtPwd)
		strSQL=select * from tblUsers Where  & _
		 Username= &  strUser  & _
		 and Password= strPwd  & 
		myCon.Open Chuoi ket noi
		Rst.Open strSQL, myCon, 0,3
		...



Example 14-14
------------------------------------------------------------------------------------------------
CREATE TABLE tblUsers (
	[UserID] [int] IDENTITY (1, 1) 
			NOT NULL PRIMARY KEY,
	[UserName] [varchar] (50) NOT NULL UNIQUE,
	[Password] [varchar] (10)  NOT NULL ,
	[FullName] [varchar] (50)  NULL ,
	[Address] [varchar] (100)  NULL ,
	[Email] [varchar] (30)  NULL ,
	[PhoneNo] [varchar] (20)  NULL 
)



Example 14-15
------------------------------------------------------------------------------------------------
Create table tblAccount
(
	UserID int,
	JoinDate smalldatetime default getdate(),
	Amount float default 1000
)

------------------------------------------------------------------------------------------------
Dim myCon,myRst
		Dim strSQL,strUser, strPwd
		strUser= _
		Replace(request.form(txtUser),,)
		strPwd= _
		Replace(request.form(txtPwd),,)
		strSQL=select * from tblUsers Where  & _
		 Username= &  strUser  & _
		 and Password= strPwd  & 
		myCon.Open Chuoi ket noi
		Rst.Open strSQL, myCon, 0,3
		...



Example 14-17
------------------------------------------------------------------------------------------------
<%
strConn="Provider=SQLOLEDB.1;Persist Security Info=False;"
strConn=strConn & "UID=sa;PWD=sa;Initial Catalog=mydatabase;Server=."
strSQL="select userid,username,password,"
strSQL=strSQL & "fullname  from "
strSQL=strSQL & " tblusers where username='" 
strSQL=strSQL & Request.form("txtUser") & "'"	
Dim lcConn, lcRst
set lcConn=CreateObject("ADODB.Connection")
set lcRst=CreateObject("ADODB.Recordset")
lcConn.Open strConn
lcRst.Open strSQL,lcConn
if Not lcRst.EOF then
  if  lcRst("Password")= request.form("txtPWD") then
	session("suid")=lcRst("UserID")
	session("susername")=lcRst("UserName")
	session("sfullname")=lcRst("FullName")
	response.redirect("myaccount.asp")				
   else
	session("suid") =-1
	response.redirect("login.asp")
   end if		
else
   session("suid")=-2
   response.redirect("login.asp")
end if
set lcRst=Nothing
set lsConn=Nothing
%>

Example 14-18
------------------------------------------------------------------------------------------------
CREATE TABLE tblUser
		(
	[UserName] [varchar] (50) NOT NULL PRIMARY KEY,
	[FullName] [varchar] (50)  NULL ,
	[Address] [varchar] (100)  NULL ,
	[Email] [varchar] (30)  NULL ,
	[PhoneNo] [varchar] (20)  NULL 
)



Example 14-19
------------------------------------------------------------------------------------------------
CREATE TABLE tblModule
		(
	[ModuleID] [int] Identity(1,1)
	NOT NULL PRIMARY KEY,
	[ModuleName] [varchar] (50) NOT NULL,		 
)




Example 14-20
------------------------------------------------------------------------------------------------
CREATE TABLE tblPermission
		(
	[ModuleID] [int] NOT NULL,
	[UserName] [varchar] (50)  NOT NULL ,
	[Permission] [char] (1) NOT NULL
	Primary key (ModuleID, UserName	, Prermission)
)

Example 14-21
------------------------------------------------------------------------------------------------
create table tblTables
(
ModuleID int,
TableName varchar(50),
ObjectType char(1) default 'T'
primary key (ModuleID,TableName)
)

Example 14-22
------------------------------------------------------------------------------------------------
create proc AddUserName
	@username varchar(50),
	@password varchar(10),
	@fullname varchar(50),
	@address varchar(50),
	@email varchar(50),
	@phoneno varchar(30)
as
	set nocount off
	declare @result int
	if not exists(select name from master.dbo.syslogins where name=@username)
	  begin
		exec sp_addlogin @username,@password,'mydatabase'
		exec sp_adduser @username , @username 
		insert into tbluser(username,fullname,address,email,phoneno)
		values(@username,@fullname,@address,@email,@phoneno)
		set @result=1
	  end
	else
		set @result=0
	set nocount on
	select @result as result



Example 14-23
------------------------------------------------------------------------------------------------
EXEC AddUserName 'huukhang','123456',
'huukhang','huukhang.com',
'support@huukhang.com','095 880 7447'

Example 14-24
------------------------------------------------------------------------------------------------

Private Sub AddUser()
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset	
    con.Open "Provider=SQLOLEDB.1;Persist " & _
    " Security Info=False;UID=sa;PWD=sa; " & _
    " Initial Catalog=Northwind;Server=."
    rs.Open "AddUserName 'huukhang', " & _
    "'123456','huukhang','huukhang.com', " & _
    "'support@huukhang.com','0958807447'",con
    If  rs.EOF Then
        MsgBox rs("Result")
    End If
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
End Sub


Example 14-25
------------------------------------------------------------------------------------------------

create proc RemoveUserName
	@username varchar(50)
as
	set nocount off
	declare @result int
	if  exists(select name from master.dbo.syslogins where name=@username)
	  begin
		exec sp_dropuser @username 
		exec sp_droplogin @username		
		delete from tbluser where username=@username
		set @result=1
	  end
	else
		set @result=0
	set nocount on
	select @result as result

Example 14-26
------------------------------------------------------------------------------------------------
EXEC RemoveUserName 'huukhang'

Example 14-27
------------------------------------------------------------------------------------------------
Private Sub AssignPermission( _
ByVal moduleid As Integer, _
    ByVal loginname As String, _
    ByVal permission As String)
    Dim strSQL As String
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    con.Open "Provider=SQLOLEDB.1;Persist " & _
    " Security Info=False;UID=sa;PWD=sa; " & _
    " Initial Catalog=mydatabase;Server=."
    rs.Open "select TableName,ObjectType from tblTables " & _
    " where ModuleID='" & moduleid & "'", con
    Do Until rs.EOF
        If rs("ObjectType") = "T" Or rs("ObjectType") = "V" Then
            If permission = "F" Then
                strSQL = "GRANT SELECT, INSERT, "
                strSQL = strSQL & " UPDATE, DELETE ON "
                strSQL = strSQL & rs("TableName") & " TO " & loginname
            ElseIf permission = "R" Then
                strSQL = "GRANT SELECT  ON "
                strSQL = strSQL & rs("TableName") & " TO " & loginname
            Else
                strSQL = "DENY SELECT, INSERT, UPDATE, "
                strSQL = strSQL & " DELETE ON " & rs("TableName")
                strSQL = strSQL & " TO " & loginname
            End If
        Else
            If permission = "F" Then
                strSQL = "GRANT EXEC ON " & rs("TableName")
                strSQL = strSQL & " TO " & loginname
            Else
                strSQL = "DENY EXEC ON " & rs("TableName")
                strSQL = strSQL & " TO " & loginname
            End If
        End If
        con.Execute strSQL
    rs.MoveNext
    Loop
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
End Sub


Example 14-28
------------------------------------------------------------------------------------------------

create proc Permission
	@moduleid int,
	@username varchar(50),
	@permission char(1)
as	
	if exists(select * from tblPermission 
		where username=@username 
		and moduleid=@moduleid)
		update tblPermission
		set Permission=@permission
		where username=@username 
		and moduleid=@moduleid
	else
		insert into tblPermission
		values(@moduleid,@username,@permission)



Example 14-29
------------------------------------------------------------------------------------------------
Private Sub Command2_Click()
    Call AssignPermission(1, "huukhang", "R")
End Sub
